﻿using Microsoft.AspNetCore.Http;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;

namespace ST.Common.Util.Office
{
	public class NPOIExcelHelper
	{
		/// <summary>
		/// 生成Excel文件（无标题，隔行换色）
		/// </summary>
		/// <param name="context"></param>
		/// <param name="colsHeaderInfo"></param>
		/// <param name="footerData"></param>
		/// <param name="autoInfoIndex"></param>
		/// <returns></returns>
		protected IWorkbook GenerateExcel<T>(IEnumerable<ExcelColumn> columns, IEnumerable<ExcelColumn> footer, IEnumerable<T> lst, bool autoGenRowNum, ExcelFileFormat fileFormat = ExcelFileFormat.XLST)
		{
			if (columns == null)
			{
				throw new ArgumentNullException("输出列不能为空");
			}
			IWorkbook wb = null;
			#region 导出到Excel文件中
			switch (fileFormat)
			{
				case ExcelFileFormat.XLST:
					wb = new XSSFWorkbook();
					break;
				case ExcelFileFormat.XLS:
					wb = new HSSFWorkbook();
					break;
				default:
					throw new ArgumentException("文件格式不支持");
			}
			ISheet sheet = wb.CreateSheet();
			//#region 设置打印相关
			////sheet.DefaultRowHeightInPoints = 24;
			////sheet.FitToPage = false;//设置分页必须
			////sheet.Autobreaks = true;//设置打印虚线
			////sheet.PrintSetup.Landscape = false;//设置横向（true），纵向（false）
			////sheet.PrintSetup.PaperSize = (short)PaperSize.A4;
			////sheet.SetMargin(MarginType.HeaderMargin, (double)0.52);//页眉间距 根据Excel中的设置/2.5
			////sheet.SetMargin(MarginType.FooterMargin, (double)0.52);//页脚间距
			////sheet.SetMargin(MarginType.LeftMargin, (double)0.32);//左间距
			////sheet.SetMargin(MarginType.RightMargin, (double)0.12);//右间距
			////sheet.SetMargin(MarginType.TopMargin, (double)0.32);//上间距
			////sheet.SetMargin(MarginType.BottomMargin, (double)1);//下间距
			//#endregion

			//#region 定义标题字体，可以自定义
			//IFont fontTitle = wb.CreateFont("宋体", 20);
			//#endregion

			//#region 定义一些样式，可以自定义
			//wb.CreateCellStyleWithBorder

			ICellStyle styleHeader = wb.CreateCellStyleWithBorder(HSSFColor.Grey25Percent.Index, HorizontalAlignment.Center, VerticalAlignment.Center);
			//ICellStyle MONEY_STYLE = GetCellStyleOfMoney(wb);
			ICellStyle styleNumber = wb.CreateCellStyleOfNumber();
			ICellStyle styleInteger = wb.CreateCellStyleOfInteger();
			//ICellStyle DEF_STYLE = GetCellStyle(wb);
			//DEF_STYLE.WrapText = true;//列自动换行

			//ICellStyle FOOTER_STYLE = GetCellStyle(wb, HSSFColor.LightCornflowerBlue.Index, HorizontalAlignment.Center, VerticalAlignment.Center);
			//#endregion

			int i, j;
			#region 解析表基本信息的配置信息
			PropertyInfo[] properties = typeof(T).GetProperties();
			i = autoGenRowNum ? 1 : 0;//设置excel列索引
			foreach (var col in columns)
			{
				col.ColumnIndex = i++;
				col.PropertyIndex = -1;
				for (j = 0; j < properties.Length; j++)
				{
					if (properties[j].Name == col.PropertyName)
					{
						col.PropertyIndex = j;
						break;
					}
				}
			}
			//int columnSize;
			//List<ExcelColumn> lstColsHeader = new List<ExcelColumn>();
			//for (i = 0; i < colsInfo.Length; i++)
			//{
			//	string[] cols = colsInfo[i].Split('@');
			//	lstColsHeader.Add(new ExcelColumn(cols[0], cols[1], cols[2], cols[3], cols[4], autoInfoIndex ? (i + 1) : i));//数据库列名，标签名，类型，格式化字符串，宽度
			//}
			//if (autoInfoIndex)
			//{
			//	columnSize = colsInfo.Length + 1;
			//	lstColsHeader.Add(new ExcelColumn("", "序号", "", "", "", 0));
			//}
			//else
			//{
			//	columnSize = colsInfo.Length;
			//}
			#endregion

			int rowIndex = 0;
			int cellIndex = 0;
			int columnCount = 0;
			#region 生成并写入表头
			IRow rowHeader = sheet.CreateRow(rowIndex++);
			cellIndex = 0;
			if (autoGenRowNum)//生成序号列
			{
				columnCount += 1;
				ICell cell = rowHeader.CreateCell(cellIndex++);
				cell.CellStyle = styleHeader;
				cell.SetCellValue("序号");
			}
			foreach (var col in columns)
			{
				columnCount += 1;
				ICell cell = rowHeader.CreateCell(cellIndex++);
				cell.CellStyle = styleHeader;
				cell.SetCellValue(col.ColumnName);

				#region 设置列宽
				if (col.ColumnWidth > 0)
				{
					sheet.SetColumnWidth(col.ColumnIndex, col.ColumnWidth * 256);
				}
				#endregion
			}
			#endregion

			#region 写入数据
			if (lst != null)
			{
				int totalCount = 0;
				foreach (var item in lst)
				{
					cellIndex = 0;
					#region 创建数据列
					IRow rowItem = sheet.CreateRow(rowIndex++);
					if (autoGenRowNum)
					{
						ICell cell = rowItem.CreateCell(cellIndex++);
						cell.SetCellValue(++totalCount);
					}
					foreach (var col in columns)
					{
						ICell cell = rowItem.CreateCell(cellIndex++);
						#region 设置表格列的类型
						switch (col.ColumnType)
						{
							case ExcelColumnType.Decimal:
							case ExcelColumnType.Float:
								{
									cell.SetCellType(CellType.Numeric);
									cell.CellStyle = styleNumber;
									if (col.PropertyIndex >= 0)
									{
										cell.SetCellValue(Convert.ToString(properties[col.PropertyIndex].GetValue(item)));
									}
								}
								break;
							case ExcelColumnType.Int:
								{
									cell.SetCellType(CellType.Numeric);
									cell.CellStyle = styleInteger;
									if (col.PropertyIndex >= 0)
									{
										cell.SetCellValue(Convert.ToString(properties[col.PropertyIndex].GetValue(item)));
									}
								}
								break;
							case ExcelColumnType.Bool:
								{
									cell.SetCellType(CellType.Boolean);
								}
								break;
							case ExcelColumnType.DateTime:
								{
									cell.SetCellType(CellType.Boolean);
									if (col.PropertyIndex >= 0)
									{
										var dt = (DateTime)properties[col.PropertyIndex].GetValue(item);
										if (string.IsNullOrEmpty(col.ColumnFormat))
										{
											cell.SetCellValue(dt.ToString("yyyy-MM-dd HH:mm:ss"));
										}
										else
										{
											cell.SetCellValue(dt.ToString(col.ColumnFormat));
										}
									}
								}
								break;
							default:
								{
									if (col.PropertyIndex >= 0)
									{
										cell.SetCellValue(Convert.ToString(properties[col.PropertyIndex].GetValue(item)));
									}
								}
								break;
						}
						#endregion
					}
					#endregion
				}
			}
			#endregion

			#region 写入表尾
			if (footer != null)
			{
				#region 生成表尾单元格
				IRow rowFooter = sheet.CreateRow(rowIndex++);
				for (i = 0; i < columnCount; i++)
				{
					ICell cell = rowFooter.CreateCell(i);
					//cell.CellStyle = styleFooter;
				}
				cellIndex = 0;
				foreach (var col in footer)
				{
					col.ColumnIndex = cellIndex;

					rowFooter.Cells[col.ColumnIndex].SetCellValue(col.StringValue);

					#region 设置合并表尾
					if (col.ColumnSpan > 1)
					{
						sheet.MergeCell(rowFooter, col.ColumnIndex, col.ColumnSpan);
					}
					#endregion
					cellIndex += (col.ColumnSpan > 0 ? col.ColumnSpan : 1);
				}
				#endregion
			}
			#endregion

			#endregion

			return wb;
		}

		/// <summary>
		/// 获取字母表示的列的数字索引（以0开始）
		/// </summary>
		/// <param name="excelAlphaName"></param>
		/// <returns></returns>
		public static int GetColumnNum(string excelAlphaName)
		{
			try
			{
				if (string.IsNullOrEmpty(excelAlphaName) || !new Regex("^[A-Za-z]+$").IsMatch(excelAlphaName))
				{
					return -1;
				}
				int i = excelAlphaName.Length - 1;
				var excelAlphaNameUpper = excelAlphaName.ToUpper();
				int num = 0;
				while (i >= 0)
				{
					num += (excelAlphaNameUpper[i] - 64) * (int)Math.Pow(26, excelAlphaName.Length - i - 1);//i-65(A)+1
					i--;
				}
				return num;
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}

		/// <summary>
		/// 获取excel列字母名
		/// </summary>
		/// <param name="columnNum">列序号（以1开始）</param>
		/// <returns></returns>
		public static string GetColumnAlphaName(int columnNum)
		{

			if (columnNum < 1)
			{
				return string.Empty;
			}
			StringBuilder buf = new StringBuilder();
			while (columnNum > 0)
			{
				columnNum = columnNum - 1;
				buf.Insert(0, char.ConvertFromUtf32(columnNum % 26 + 65));
				columnNum = columnNum / 26;
			}
			return buf.ToString();
		}

		public static bool GetCellRowAndColumnNum(string cellAlphaName, out int rowNum, out int columnNum)
		{
			try
			{
				rowNum = 0;
				columnNum = 0;
				return true;
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}

	}
}
